﻿Imports MySql.Data.MySqlClient

Namespace DB

    Public Class MysqlHelper

        Private ReadOnly RETURNVALUE As String = "RETURNVALUE"
        '接続文字列を設定
        'Dim mSQLconn As New MySqlConnection
        'mSQLconn.ConnectionString = "Database=mldn;Data Source=127.0.0.1;User Id=admin;Password=admin"
        'Public mSQLconn As New MySqlConnection("Database=mldn;Data Source=127.0.0.1;User Id=admin;Password=admin")
        Public mSQLconn As New MySqlConnection( _
                String.Format("Port={0};Data Source={1};Database={2};User ID={3};Password={4};CharSet={5}", _
                        "3306", "127.0.0.1", "mldn", "admin", "admin", "utf8"))
        Private SQLCONNECTSTR As String = String.Format("Port={0};Data Source={1};Database={2};User ID={3};Password={4}", _
                        "3306", "127.0.0.1", "mldn", "admin", "admin")
        Public mSQLconn2 As New MySqlConnection( _
                String.Format("Data Source={0};Database={1};User ID={2};Password={3};CharSet={4}", _
                        "101.110.219.178", "xiuersoft", "xiuersoft", "xiuersoft2013!", "utf8"))
        'Public conn As New MySqlConnection(SQLCONNECTSTR)


        Sub New()

        End Sub

        ''' <summary>
        ''' 
        ''' </summary>
        Private Sub Open()
            ' 
            If mSQLconn Is Nothing Then
                'mSQLconn = new MySqlConnection(ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString);
                mSQLconn = New MySqlConnection(SQLCONNECTSTR)
            End If

            If mSQLconn.State = ConnectionState.Closed Then
                Try
                    mSQLconn.Open()
                Catch ex As Exception
                    SystemError.SystemLog(ex.Message)
                Finally
                End Try
            End If
        End Sub

        ''' <summary>
        ''' 
        ''' </summary>
        Public Sub Close()
            If mSQLconn IsNot Nothing Then
                If mSQLconn.State = ConnectionState.Open Then
                    mSQLconn.Close()
                End If
            End If
        End Sub

        ''' <summary>
        ''' 
        ''' </summary>
        Public Sub Dispose()
            ' 
            If mSQLconn IsNot Nothing Then
                mSQLconn.Dispose()
                mSQLconn = Nothing
            End If
        End Sub

        'DataTableを取得
        Public Function GetSQLdataTbl(ByVal sqlstr As String) As DataTable
            Dim mDataSet = New DataSet
            Dim mDataTbl = New DataTable
            Dim mDataAdper As Common.DataAdapter
            mDataSet.Clear()
            Try
                Open()
                mDataAdper = New MySqlDataAdapter(sqlstr, mSQLconn)
                'DatasetをFill
                mDataAdper.Fill(mDataSet)
                If mDataSet.Tables.Count = 1 Then
                    mDataTbl = mDataSet.Tables(0)
                End If
            Catch ex As MySqlException
                MsgBox(ex.Message, "データベースエラー")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                mDataSet.Dispose()
                Close()
            End Try
            Return mDataTbl
        End Function

        'DataSetを取得
        Public Function GetSQLdataSet(ByVal sqlstr As String, _
                Optional ByVal prams As MySqlParameter() = Nothing) As DataSet
            Dim mDataSet = New DataSet
            Dim mDataAdper As Common.DataAdapter
            mDataSet.Clear()
            Try
                Open()
                mDataAdper = New MySqlDataAdapter(sqlstr, mSQLconn)
                'DatasetをFill
                mDataAdper.Fill(mDataSet)
            Catch ex As MySqlException
                MsgBox(ex.Message, "データベースエラー")
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                Close()
            End Try
            Return mDataSet
        End Function

        'コマンド
        Public Function UpSQL(ByVal sqlstr As String) As Boolean
            Dim mCommand As MySqlCommand = New MySqlCommand()
            Try
                Open()
                mCommand.Connection = mSQLconn
                mCommand.CommandText = sqlstr
                mCommand.CommandType = CommandType.Text
                mCommand.ExecuteNonQuery()
                'コマンド成功の場合、True
                UpSQL = True
            Catch ex As Exception
                'コマンド失敗の場合、False
                UpSQL = False
            Finally
                Close()
            End Try
            Return UpSQL
        End Function


        'コマンド
        Public Function RunProc0(ByVal sqlstr As String) As Boolean
            Dim mCommand As MySqlCommand = New MySqlCommand()
            Try
                RunProc0 = False
                Open()

                With mCommand
                    .Connection = mSQLconn                  '数据连接
                    .CommandType = CommandType.StoredProcedure            '表明这是一个存储过程
                    .CommandText = "Code_Search_Code"         '存储过程名称
                    .Parameters.Add(New MySqlParameter("@a", MySqlDbType.UInt16))
                    .Parameters("@a").Value = 10
                    .Parameters.Add(New MySqlParameter("@return", MySqlDbType.Int16))
                    .Parameters("@return").Direction = ParameterDirection.ReturnValue
                    .ExecuteNonQuery()                                  '执行此存储过程，返回记录集
                    Return .Parameters("@return").Value.ToString()
                End With


                'コマンド成功の場合、True
                RunProc0 = True
            Catch MyException As MySqlException
                Console.WriteLine("Stored procedure error: MySQL code: " & _
                        MyException.Number & "  " & _
                        MyException.Message)
            Catch ex As Exception
                'コマンド失敗の場合、False
                RunProc0 = False
            Finally
                Close()
            End Try
            Return RunProc0
        End Function

        '======================================================================================
        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <returns></returns>
        Public Function RunProc(ByVal procName As String) As Integer
            Dim cmd As MySqlCommand = CreateProcCommand(procName, Nothing)
            Try
                '
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try

            '
            Return CInt(cmd.Parameters(RETURNVALUE).Value)
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <returns></returns>
        Public Function RunProc(ByVal procName As String, ByVal prams As MySqlParameter()) As Integer
            Dim cmd As MySqlCommand = CreateProcCommand(procName, prams)
            Try
                '
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try

            '
            Return CInt(cmd.Parameters(RETURNVALUE).Value)
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <returns></returns>
        Public Function RunProc3(ByVal procName As String, ByVal prams As MySqlParameter()) As String
            Dim cmd As MySqlCommand = CreateProcCommand(procName, prams)
            Try
                '
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try

            '
            Return cmd.Parameters(RETURNVALUE).Value
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <returns></returns>
        Public Function RunProc_ForReturnString(ByVal procName As String, ByVal prams As MySqlParameter()) As String
            Dim cmd As MySqlCommand = CreateProcCommand_ForReturnString(procName, prams)
            Try
                '
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try

            '
            Return cmd.Parameters(RETURNVALUE).Value
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="dataReader"></param>
        Public Sub RunProc(ByVal procName As String, ByRef dataReader As MySqlDataReader)
            '
            Dim cmd As MySqlCommand = CreateProcCommand(procName, Nothing)

            Try
                '
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                dataReader = Nothing
                '
                SystemError.SystemLog(ex.Message)
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <param name="dataReader"></param>
        Public Sub RunProc(ByVal procName As String, ByVal prams As MySqlParameter(), ByRef dataReader As MySqlDataReader)
            '
            Dim cmd As MySqlCommand = CreateProcCommand(procName, prams)

            Try
                '
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                dataReader = Nothing
                '
                SystemError.SystemLog(ex.Message)
            End Try
        End Sub


        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <param name="dataSet"></param>
        Public Sub RunProc(ByVal procName As String, ByRef dataSet As DataSet, Optional ByVal prams As MySqlParameter() = Nothing)
            If dataSet Is Nothing Then
                dataSet = New DataSet()
            End If
            '
            Dim da As MySqlDataAdapter = CreateProcDataAdapter(procName, prams)

            Try
                '
                da.Fill(dataSet)
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try
        End Sub


        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <param name="DataTable"></param>
        Public Sub RunProc(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal prams As MySqlParameter() = Nothing)
            If dataTable Is Nothing Then
                dataTable = New DataTable()
            End If
            '
            Dim da As MySqlDataAdapter = CreateProcDataAdapter(procName, prams)

            Try
                '
                da.Fill(dataTable)
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <param name="DataTable"></param>
        Public Sub RunProc2(ByVal procName As String, ByRef dataTable As DataTable, Optional ByVal item As DB.MysqlUtilities.STC_Item() = Nothing, Optional ByVal prams As MySqlParameter() = Nothing)
            If dataTable Is Nothing Then
                dataTable = New DataTable()
            End If
            If item IsNot Nothing Then
                For i As Integer = 0 To item.Length - 1
                    procName += item(i).SqlKey
                    'tbl.Sql += item[i].ItemId + "+'%'";
                    procName += item(i).ItemId
                Next
            End If
            '
            Dim da As MySqlDataAdapter = CreateProcDataAdapter(procName, prams)

            Try
                '
                da.Fill(dataTable)
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <returns></returns>
        Public Function RunSQL(ByVal cmdText As String) As Integer
            Dim cmd As MySqlCommand = CreateSQLCommand(cmdText, Nothing)
            Try
                '
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try

            '
            Return CInt(cmd.Parameters(RETURNVALUE).Value)
        End Function
        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <param name="prams">SQL</param>
        Public Function RunSQL(ByVal cmdText As String, ByVal prams As MySqlParameter()) As Integer
            Dim cmd As MySqlCommand
            Dim cnt As Integer = 0
            Try
                '
                cmd = CreateSQLCommand(cmdText, prams)
                cnt = cmd.ExecuteNonQuery()

                Return cnt
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
                Return -1
            Finally
                '
                Close()
            End Try
        End Function

        '''' <summary>
        '''' SQL
        '''' </summary>
        '''' <param name="cmdText">SQL</param> 
        '''' <param name="dataReader">DataReader</param>
        Public Sub RunSQL(ByVal cmdText As String, ByRef dataReader As MySqlDataReader)
            '
            Dim cmd As MySqlCommand = CreateSQLCommand(cmdText, Nothing)

            Try
                '
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                dataReader = Nothing
                '
                SystemError.SystemLog(ex.Message)
            End Try
        End Sub

        '''' <summary>
        '''' SQL
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <param name="prams">SQL</param>
        '''' <param name="dataReader">DataReader</param>
        Public Sub RunSQL(ByVal cmdText As String, ByVal prams As MySqlParameter(), ByRef dataReader As MySqlDataReader)
            'Command
            Dim cmd As MySqlCommand = CreateSQLCommand(cmdText, prams)

            Try
                '
                dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            Catch ex As Exception
                dataReader = Nothing
                '
                SystemError.SystemLog(ex.Message)
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <param name="dataSet">DataSet</param>
        Public Sub RunSQL(ByVal cmdText As String, ByRef dataSet As DataSet)
            If dataSet Is Nothing Then
                dataSet = New DataSet()
            End If
            'SqlDataAdapter
            Dim da As MySqlDataAdapter = CreateSQLDataAdapter(cmdText, Nothing)

            Try
                '
                da.Fill(dataSet)
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <param name="prams">SQL</param>
        '''' <param name="dataSet">DataSet</param>
        Public Sub RunSQL(ByVal cmdText As String, ByVal prams As MySqlParameter(), ByRef dataSet As DataSet)
            If dataSet Is Nothing Then
                dataSet = New DataSet()
            End If
            'SqlDataAdapter
            Dim da As MySqlDataAdapter = CreateProcDataAdapter(cmdText, prams)

            Try
                '
                da.Fill(dataSet)
            Catch ex As Exception
                '
                SystemError.SystemLog(ex.Message)
            Finally
                '
                Close()
            End Try
        End Sub

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="cmdText">SQL</param>
        '''' <param name="prams">SQL</param>
        Public Function GetSQLdataTbl(ByVal cmdText As String, ByVal prams As MySqlParameter()) As DataTable
            Dim dt As New DataTable
            'SqlDataAdapter
            Dim da As MySqlDataAdapter = CreateProcDataAdapter(cmdText, prams)

            Try
                '
                da.Fill(dt)
                Return dt
            Catch ex As Exception
                ''''''
                SystemError.SystemLog(ex.Message)
                Return Nothing
            Finally
                '
                Close()
            End Try
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <returns></returns>
        Private Function CreateProcCommand(ByVal procName As String, ByVal prams As MySqlParameter()) As MySqlCommand
            '
            Open()

            '
            Dim cmd As New MySqlCommand(procName, mSQLconn)
            cmd.CommandType = CommandType.StoredProcedure

            '
            If prams IsNot Nothing Then
                For Each parameter As MySqlParameter In prams
                    cmd.Parameters.Add(parameter)
                Next
            End If

            'ReturnValue
            cmd.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, False, 0, _
             0, String.Empty, DataRowVersion.[Default], Nothing))

            'SqlCommand
            Return cmd
        End Function

        '''' <summary>
        '''' 
        '''' </summary>
        '''' <param name="procName"></param>
        '''' <param name="prams"></param>
        '''' <returns></returns>
        Private Function CreateProcCommand_ForReturnString(ByVal procName As String, ByVal prams As MySqlParameter()) As MySqlCommand
            '
            Open()

            '
            Dim cmd As New MySqlCommand(procName, mSQLconn)
            cmd.CommandType = CommandType.StoredProcedure

            '
            If prams IsNot Nothing Then
                For Each parameter As MySqlParameter In prams
                    cmd.Parameters.Add(parameter)
                Next
            End If

            'ReturnValue
            'cmd.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, False, 0, _
            ' 0, String.Empty, DataRowVersion.[Default], Nothing))
            cmd.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.String, 100, ParameterDirection.ReturnValue, False, 0, _
             0, String.Empty, DataRowVersion.[Default], Nothing))


            'SqlCommand
            Return cmd
        End Function

        Private Function CreateSQLCommand(ByVal cmdText As String, ByVal prams As MySqlParameter()) As MySqlCommand
            '
            Open()

            '
            Dim cmd As New MySqlCommand(cmdText, mSQLconn)

            '
            If prams IsNot Nothing Then
                For Each parameter As MySqlParameter In prams
                    cmd.Parameters.Add(parameter)
                Next
            End If

            '
            cmd.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, False, 0, _
             0, String.Empty, DataRowVersion.[Default], Nothing))

            '
            Return cmd
        End Function

        Private Function CreateProcDataAdapter(ByVal procName As String, ByVal prams As MySqlParameter()) As MySqlDataAdapter
            '
            Open()

            '
            Dim da As New MySqlDataAdapter(procName, mSQLconn)
            'da.SelectCommand.CommandType = CommandType.StoredProcedure; //2013/02/26 shin

            '
            If prams IsNot Nothing Then
                For Each parameter As MySqlParameter In prams
                    da.SelectCommand.Parameters.Add(parameter)
                Next
            End If

            '
            da.SelectCommand.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, False, 0, _
             0, String.Empty, DataRowVersion.[Default], Nothing))

            Return da
        End Function

        Private Function CreateSQLDataAdapter(ByVal cmdText As String, ByVal prams As MySqlParameter()) As MySqlDataAdapter
            Open()

            Dim da As New MySqlDataAdapter(cmdText, mSQLconn)

            If prams IsNot Nothing Then
                For Each parameter As MySqlParameter In prams
                    da.SelectCommand.Parameters.Add(parameter)
                Next
            End If

            da.SelectCommand.Parameters.Add(New MySqlParameter(RETURNVALUE, MySqlDbType.Int16, 4, ParameterDirection.ReturnValue, False, 0, _
             0, String.Empty, DataRowVersion.[Default], Nothing))

            Return da
        End Function

        Public Function CreateParam(ByVal ParamName As String, ByVal DbType As MySqlDbType, ByVal Size As Int32, ByVal Direction As ParameterDirection, ByVal Value As Object) As MySqlParameter
            Dim param As MySqlParameter

            If Size > 0 Then
                param = New MySqlParameter(ParamName, DbType, Size)
            Else
                param = New MySqlParameter(ParamName, DbType)
            End If

            param.Direction = Direction
            If Not (Direction = ParameterDirection.Output AndAlso Value Is Nothing) Then
                param.Value = Value
            End If

            Return param
        End Function

        Public Function CreateInParam(ByVal ParamName As String, ByVal Value As Object, ByVal DbType As MySqlDbType, ByVal Size As Integer) As MySqlParameter
            Return CreateParam(ParamName, DbType, Size, ParameterDirection.Input, Value)
        End Function

        Public Function CreateOutParam(ByVal ParamName As String, ByVal DbType As MySqlDbType, ByVal Size As Integer) As MySqlParameter
            Return CreateParam(ParamName, DbType, Size, ParameterDirection.Output, Nothing)
        End Function

        Public Function CreateReturnParam(ByVal ParamName As String, ByVal DbType As MySqlDbType, ByVal Size As Integer) As MySqlParameter
            Return CreateParam(ParamName, DbType, Size, ParameterDirection.ReturnValue, Nothing)
        End Function

#Region "Insert"

        ''' <summary>
        ''' 大批量添加数据ds.Tables[0] 表名strTblName(数据库表名称)
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="strTblName"></param>
        ''' <returns>受影响的行数</returns>
        Public Function SqlBulkInsert(ByVal dt As DataTable, ByVal strTblName As String) As Integer
            Dim affect As Integer = 0
            Try
                Dim myCommand As New MySqlCommand("select * from " & strTblName, mSQLconn)
                Dim myAdapter As New MySqlDataAdapter(myCommand)
                Dim myCommandBuilder As New MySqlCommandBuilder(myAdapter)
                myAdapter.InsertCommand = myCommandBuilder.GetInsertCommand()

                For Each dr As DataRow In dt.Rows
                    If dr.RowState <> DataRowState.Added Then
                        dr.SetAdded()
                    End If
                Next
                Open()
                affect = myAdapter.Update(dt)
                ' ds.AcceptChanges();
                ' return ds; 
                Close()
            Catch err As Exception
                Close()
                Throw err
            End Try
            Return affect
        End Function
#End Region

#Region "Update"
        ''' <summary>
        ''' 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="strTblName"></param>
        ''' <returns>受影响的行数</returns>
        Public Function SqlBulkUpdate(ByVal dt As DataTable, ByVal strTblName As String) As Integer
            Dim affect As Integer = 0
            Try
                'foreach (DataRow dr in dt.Rows)
                '{
                '    if (dr.RowState == DataRowState.Added)
                '    {
                '        dr[0] = dr[0];
                '        dr.AcceptChanges();
                '        dr.SetModified();
                '    }
                '}           

                Dim myCommand As New MySqlCommand("select * from " & strTblName, mSQLconn)
                Dim myAdapter As New MySqlDataAdapter(myCommand)
                'myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                Dim myCommandBuilder As New MySqlCommandBuilder(myAdapter)
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand()
                Open()
                affect = myAdapter.Update(dt)
                ' ds.AcceptChanges();
                ' return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
                Close()
            Catch err As Exception
                Close()
                Throw err
            End Try
            Return affect
        End Function
        ''' <summary>
        ''' 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="strTblName"></param>
        ''' <returns>受影响的行数</returns>
        Public Function SqlBulkUpdate2(ByVal dt As DataTable, ByVal strSQL As String) As Integer
            Dim affect As Integer = 0
            Try
                'foreach (DataRow dr in dt.Rows)
                '{
                '    if (dr.RowState == DataRowState.Added)
                '    {
                '        dr[0] = dr[0];
                '        dr.AcceptChanges();
                '        dr.SetModified();
                '    }
                '}           

                Dim myCommand As New MySqlCommand(strSQL, mSQLconn)
                Dim myAdapter As New MySqlDataAdapter(myCommand)
                'myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
                Dim myCommandBuilder As New MySqlCommandBuilder(myAdapter)
                myAdapter.UpdateCommand = myCommandBuilder.GetUpdateCommand()
                Open()
                affect = myAdapter.Update(dt)
                ' ds.AcceptChanges();
                ' return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
                Close()
            Catch err As Exception
                Close()
                Throw err
            End Try
            Return affect
        End Function

        ''' <summary>
        ''' 大批量更改数据ds.Tables[0] 表名strTblName(数据库表名称,表需要有主键)
        ''' </summary>
        ''' <param name="dt"></param>
        ''' <param name="strTblName"></param>
        ''' <returns>受影响的行数</returns>
        Public Function SqlBulkUpdate(ByVal dt As DataTable, ByVal strTblName As String, ByVal updatecmd As MySqlCommand) As Integer
            Dim affect As Integer = 0
            Try
                'foreach (DataRow dr in dt.Rows)
                '{
                '    if (dr.RowState == DataRowState.Added)
                '    {
                '        dr[0] = dr[0];
                '        dr.AcceptChanges();
                '        dr.SetModified();
                '    }
                '}           


                Dim myCommand As New MySqlCommand("select * from " & strTblName, mSQLconn)
                Dim myAdapter As New MySqlDataAdapter(myCommand)
                Dim myCommandBuilder As New MySqlCommandBuilder(myAdapter)
                myAdapter.UpdateCommand = updatecmd
                Open()
                affect = myAdapter.Update(dt)
                ' ds.AcceptChanges();
                ' return ds;    //数据集的行状态在更新后会都变为: UnChange,在这次更新后客户端要用返回的ds 
                Close()
            Catch err As Exception
                Close()
                Throw err
            End Try
            Return affect
        End Function
#End Region
    End Class
End Namespace
